import pandas as pd
import pymysql
import uuid
import datetime
import random
import pinyin.cedict
import json
from urllib import request
import time

db = pymysql.connect( "10.2.5.101", "root", "Kc@123456", "helmet_operate_sit" )
cursor = db.cursor()


def getMaterialsId(sheetName):
    problem_type_sql = 'SELECT id FROM kc_data_dictionary_t WHERE obj_name = \'{0}\' and delete_flag = \'{1}\''.format(
        str( sheetName ).strip(), '0' )
    cursor.execute( problem_type_sql )
    fetchone = cursor.fetchone()
    return fetchone


def savedata(saveSql):
    for sql in saveSql:
        cursor.execute( sql )
        db.commit()


if __name__ == '__main__':
    fileName = '/Users/xuyihang/Downloads/副本光启智能防控头盔N901八版本BOM-202009.xlsx'
    xl = pd.ExcelFile( fileName )
    sheetNames = xl.sheet_names
    save_sql = []
    for sheetName in sheetNames:
        materials_id = getMaterialsId( sheetName )
        if materials_id is not None:
            excel = pd.read_excel( fileName, usecols=[0, 1, 6], sheet_name=sheetName )
            excel_data_file = excel.values.tolist()
            for data in excel_data_file:
                if len( str( data[1] ) ) == 14:

                    save_sql_string = 'INSERT INTO kc_helmet_material_version_t(id,material_code,helmet_version_id,machine_consumpt) values (UUID(),\'{0}\',\'{1}\',\'{2}\')'.format(
                        data[1], materials_id[0], round(data[2],3) )
                    save_sql.append( save_sql_string )

    savedata( save_sql )
